package cn.agiledata.bank.scores.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.springframework.dao.DataAccessException;

import cn.agiledata.bank.common.util.DateUtil;
import cn.agiledata.bank.hibernateBean.Account;
import cn.agiledata.bank.hibernateBean.ScoresHis;
import com.vnv.infrastructure.logic.IPBHibernate3BaseMgr;

public class ScoresDAOImpl extends IPBHibernate3BaseMgr implements IScoresDAO {
	public List getScoresRule(String scoresSource, 
			String grading, Date transDate) throws DataAccessException{
		String dateStr = " to_date(' " + DateUtil.date2String(transDate)
		+ " 00:00:00','YYYY-MM-DD HH24:MI:SS')";
		StringBuffer clause = new StringBuffer("  from ScoresRules s where ");
		clause.append(" s.scoresSource.id = ").append(scoresSource);
		clause.append(" and s.groups = ").append(grading);
		clause.append(" and s.startdate <= ").append(dateStr);
		clause.append(" and s.enddate >= ").append(dateStr);
		return getHibernateTemplate().find(clause.toString());
	}

	public List getScoresHis(String accountId) throws DataAccessException{
		// TODO Auto-generated method stub
		StringBuffer clause = new StringBuffer(" from ScoresHis s where ");
		clause.append(" s.account.id = ").append(accountId);
		clause.append(" order by s.id desc, s.updateTime desc ");
		return getHibernateTemplate().find(clause.toString());
	}
	
	public void saveOrUpdateScoresHisWithoutFlush(ScoresHis sh) throws DataAccessException {
		this.getHibernateTemplate().saveOrUpdate(sh) ;
	}
	public void saveOrUpdateScoresHistory (ScoresHis sh) throws DataAccessException {
		this.getHibernateTemplate().saveOrUpdate(sh) ;
        this.getHibernateTemplate().flush();
	}
	//测试数据
	public List getTestDao() throws DataAccessException{
		StringBuffer clause = new StringBuffer(" from  AccountBalance ");
		return getHibernateTemplate().find(clause.toString());
	}
	//获得个人用户(test方法用)
	public List getAccountList() throws DataAccessException{
		StringBuffer clause = new StringBuffer(" from Account a where a.accountType='A' ");
		return getHibernateTemplate().find(clause.toString());
	}
	//获得默认客户等级
//	public Grading getDefaultGrading() throws DataAccessException{
//		StringBuffer clause = new StringBuffer(" from Grading g where g.id = 1");
//		return (Grading)getHibernateTemplate().find(clause.toString());
//	}
	//获得推荐人
	public List getIntroduceList(String accountId) throws DataAccessException{
		StringBuffer clause = new StringBuffer(" from Account a where a.introduceAccountId= ").append(accountId);
		return getHibernateTemplate().find(clause.toString());
	}
	//获得积分来源
	public List getScoresSource() throws DataAccessException{
		StringBuffer clause = new StringBuffer(" from ScoresSource ");
		return getHibernateTemplate().find(clause.toString());
	}
	
	public ScoresHis getScoresHis(Long accountId,String scoresSource, Long transId) throws DataAccessException{
		StringBuffer clause = new StringBuffer(" from ScoresHis s where ");
		clause.append(" s.account.id = ").append(accountId);
		clause.append(" and s.scoresRules.scoresSource.id = ").append(scoresSource);
		if(transId != null) {
			clause.append(" and s.transaction.id=").append(transId);
		}
		clause.append(" order by s.id desc, s.updateTime desc ");
		List l = getHibernateTemplate().find(clause.toString());
		if (l.size()==0){
			return new ScoresHis();
		}
		return (ScoresHis) l.get(0);
	}
	//获取当前有效的所有积分规则
	
	public List getCurScoresRule(Long id) throws DataAccessException{
		String dateStr = " to_date(' " + DateUtil.date2String(new Date())
		+ " 00:00:00','YYYY-MM-DD HH24:MI:SS')";
		StringBuffer clause = new StringBuffer("  from ScoresRules s where ");
		clause.append(" s.startdate <= ").append(dateStr);
		clause.append(" and s.enddate >= ").append(dateStr);
		clause.append(" and s.groups.id = ").append(id);
		return getHibernateTemplate().find(clause.toString());
	}

	public Double getMonthScore(Account account) {
		Double dbReturn = null;
		// 取得用户当前月度获得总积分
		// 所有的积分消费scoresActivityId均不为空
		Calendar calNow = Calendar.getInstance();
		calNow.setTime(new Date());
		String strOql = "select sum(sh.scoresAmount) from  ScoresHis sh " +
				" where sh.scoresActivityId is null " +
				" and sh.account.id=" + account.getId() +
				" and year(sh.createTime)=" + calNow.get(Calendar.YEAR) +
				" and month(sh.createTime)=" + (calNow.get(Calendar.MONTH) + 1);
		
		List listResult = getHibernateTemplate().find(strOql);
		if(listResult != null && listResult.size() > 0) {
			dbReturn = (Double)listResult.get(0);
		}
		
		if(dbReturn == null) {
			dbReturn = new Double(0);
		}
		
		return dbReturn;
	}
}
